home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Tech Arsenal 1
/
Tech Arsenal (Arsenal Computer).ISO
/
tek-03
/
123w20.zip
/
123WRITE.DOC
< prev
next >
Wrap
Text File
|
1990-11-15
|
162KB
|
4,456 lines
OVERVIEW 1
FILES INCLUDED WITH 123W20.ZIP 2
LICENSE 2
DISTRIBUTION 3
REGISTRATION 3
SUPPORT 3
REQUIREMENTS 5
GENERATING THE 123-WRITE LIBRARIES 5
MAKENEAR.BAT 6
MAKEFAR.BAT 7
COMPILING 8
CALLING SEQUENCE 9
DEFAULT SETTINGS-lFileOpen 12
DEFAULT SETTINGS-lFileAppend 13
SOME GUIDELINES 14
FINDING YOUR WAY AROUND THE SPREADSHEET 15
WHAT'S NEW? 15
lBoolean 18
lCellFunction 19
lCellMath 23
lClose 24
lCol 25
lColFunction 26
lConstMath 27
lDate 28
lDown 29
lFileAppend 30
lFileOpen 32
lGetLoc 33
lGlobal 34
lHideCol 35
lLeft 36
lLineFeed 37
lLink 38
lMultiCellMath 40
lPrintBorders 43
lPrintMargins 44
lPrintRange 45
lPrintSetup 46
lPrintTitle 47
lRange 48
lRight 49
lRowFunction 50
lSetCellForm 51
lSetCol 53
lSetCols 54
lSetDateForm 55
lSetLoc 56
lSetStrForm 57
lSetTimeForm 58
lSortKey 59
lSortRange 60
lTime 61
lTimeNow 62
lToday 63
i
lUp 64
lWriteErr 65
lWriteInt 66
lWriteNum 66
lWriteStr 67
ERRORS 68
CONTACTING the author 69
APPENDIX A - DOS Error Codes 70
Routine Reference 71
ii
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
OVERVIEW
123-Write is a collection of assembly language sub programs to
write a Lotus 1-2-3 file from within a QB 4+, QBX, BASIC 6.0 or
BASIC 7.0+ program. Not all of 1-2-3's functions and operations
are available, yet I feel that I've included some of the most
frequently needed.
In the past I've always written programs that allow the user to
specify where report output should be directed, the screen,
printer a print image file, etc. These routines were an out-
growth of two needs. The first one was just wanting to "dress
up" my code a little more, and add a little versatility. What
could be more polished than writing a report's output directly to
a 1-2-3 spreadsheet file?
The second, and more important reason, was all the work involved
in 1-2-3 to import, transfer and work with data from another
program. One commercial package offers a "Lotus 1-2-3" export
option on their report menu. When I needed just that ability
once I was a more then a little disappointed to find the output
file nothing more then a comma delimited sequential file. Im-
porting to Lotus was simple, but dates became strings, and all
the data had to be shifted around within Lotus to be of any use.
Writing a macro to handle the job was a chore, and it was quite
slow doing conversions of ASCII formatted dates etc.
123-Write will provide you with the ability to take that comma
delimited file and convert it directly into a formatted worksheet
for example. Read and write the data just the way you need it.
Generate an up to date spreadsheet from your own BASIC data files
whenever needed. You won't have to write out an ASCII sequential
file, and then write Lotus 1-2-3 Macros to read the data file
into your worksheet, and reformat it.
Lotus, and 1-2-3 are registered trademarks of Lotus Development
Corporation, and Microsoft is a registered trademark of Microsoft
Corporation, by the way.
1
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
FILES INCLUDED in the 123W20.ZIP:
123W20.LIB Skeleton .LIB file for all BASIC programs
- EXCEPT - BC 7.0+ with far strings.
123W20F.LIB Skeleton .LIB file for BC 7.0+ programs with
- FAR STRINGS ONLY -
LCOLS.BAS Source code interface for lCols routine.
LDATE.BAS Source code interface for lDate routine.
LTIME.BAS Source code interface for lTime routine.
MAKENEAR.BAT Compiles the .BAS files, adds them to
123W20.LIB, generates the non-QBX
123W20.QLB file for QB 4.0+.
MAKEFAR.BAT Compiles the .BAS files, adds them to
123W20F.LIB, generates the QBX 123W20F.QLB
QuickLib file.
123WRITE.DOC Complete documentation for version 2.0
123WRITE.BI An ASCII include file that DECLARES all the
123-Write routines.
123DEMO2.BAS Demonstration of a few of the routines.
example: QB 123DEMO2 /L 123W20.QLB
or: QBX 123DEMO2 /L 123W20F.QLB
The .LIB files are "skeletons" because they contain all the
assembly language routines, but do not contain the three BASIC
routines. The MAKENEAR and MAKEFAR batch files handle the com-
piling, insertion in the object file library and building of the
QuickLibrary for you.
LICENSE
123-Write is not PUBLIC DOMAIN software. It is distributed as
Shareware, and as such, non registered users are granted a li-
cense to use 123-Write for a limited evaluation period, for your
own noncommercial use. Use of the routines in this archive are
not to be used in any software that you sell, offer for sale,
generate in the course of your employment, or after a reasonable
evaluation period (30 days). All that requires registering the
copy.
2
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
DISTRIBUTION
You may copy and freely distribute 123-Write, as long as:
No fee is charged. (Recognized user groups and clubs may
charge a nominal fee for expenses.)
All the files included within this archive are distributed
intact and without any modification(s).
WARRANTY
This software is distributed AS IS without warranty of any kind,
express or implied, including but not limited to implied warran-
ties for the software's quality, performance, merchantability, or
fitness for any particular purpose. Neither myself or any dealer
or distributor shall be liable with respect to any liability,
loss, or damage caused, or alleged to be caused directly or
indirectly by this software.
REGISTRATION
If you use the 123-Write routines within any application that you
distribute, or in the course of your employment, registration at
$25.00 is mandatory. If you find 123-Write convenient to use, if
you find 123-Write a help to your programming, a $25.00 registra-
tion is expected.
With each registration you will receive a diskette with a li-
censed (distributable) copy of the library, be listed to receive
a diskette of the next release (if any) of 123-Write, and receive
what you may possibly find an interesting text file on the Lotus
worksheet file format. (perhaps you may be interested in reading
a worksheet file before I get finished with 123-READ, but that's
another story.)
SUPPORT
Questions will be answered when asked. Fastest method for non-
registered users may be via a call to The Off Hour Rockers BBS in
Sayreville NJ. 201-727-6917 and 201-727-6785. Settings are N81
9600/2400/1200 baud 24 hours a day. Join the BASIC conference
(j;7) after logging on, and address your message to Tom Vought
and I'll reply as soon as possible. The BBS is founded on free
spirit principles and is a little off the wall at times, no
hassle about signing on or downloads. Please be sure to use you
real name though, as "handles" are considered quite tacky. The
latest version of 123-Write is always available for download from
conference 7.
CIS ID: 70054,1367 (Infrequent, so use CIS E-Mail)
US Mail to the address on the registration form will also work
reasonably well.
A registration form for your use follows.
3
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
Please make checks payable and mail To:
Thomas J. Vought
5 John Street
Morganville, NJ 07751
Registration of: 123-Write ver 2.0
Name: _____________________________________________
Company Name: _____________________________________________
Mailing Address
Street: _____________________________________________
Town, State & Zip: _____________________________________________
Phone: ____________________________________________
Do you have a modem? ______________ Disk format ____ 3.50"
____ 5.25"
Where did you receive 123-Write from? (Just curious.)
What functions, @formulas, or other esoteric stuff would you like
to see included in any future releases of 123-Write?
Comments?:
4
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
OK, I think I have all that stuff about registration out of the
way. Only one more item. Routines are added based on user
suggestions, registered or not.
Requirements:
123-Write (2.0) was developed and tested using Microsoft's Macro
Assembler ver 5.1, Microsoft's Basic Compiler versions 6.0 and
7.1 and both QB ver 4.5 and QBX ver 7.1.
The worksheet files written are compatible with all current
versions of Lotus 1-2-3. Specific features of Release 3 are not
supported, though the spreadsheets written can be loaded by Re-
lease 3 without problem. Care should be taken to avoid calling
routines that are not supported by the target Lotus version.
I've tried to make note of Lotus version requirements in the
routine index and in the routine descriptions. If I missed or
made a blatantly greivious error.... Sorry, let me know and I'll
correct it in the next revision of the documentation.
123-Write version 2.0 requires DOS version 2.0+.
GENERATING THE 123-WRITE LIBRARIES
Creating a quick library and a linker library is performed by
either the MAKENEAR.BAT or MAKEFAR.BAT files (or both) included
with the zip file.
MAKENEAR (page 6) is for:
QB 4.0, QB 4.5 and BC 6.0
MAKEFAR (page 7) is for:
BC 7.0 near and far string support.
BC 7.1 near and far string support.
QBX 7.0 QuickLibrary
QBX 7.1 QuickLibrary
The preferred method is to work in a sub-directory that has only
the 123-Write files in it.
For either batch file, make the 123-Write directory the default.
Access to:
LIB.EXE
LINK.EXE
and your basic compiler (BC.EXE)
should be assured via your PATH statement.
The LINKer will need your quick library support module,
BQLBxx.LIB or QBXQLB.LIB either in the same directory, or pointed
to by the LIB= environment variable.
5
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
If your development environment is QB 4.0, QB 4.5 or BC 6.0:
1. Ignore the MAKEFAR.BAT batch file entirely.
2. The syntax for MAKENEAR is:
MAKENEAR compilerName qlbSupportLib [compiler / options]
This will create both the QB Quick Library, 123W20.QLB and
123W20.LIB.
The compilerName variable is usually BC, unless you keep various
versions of the compiler on your disk, as I do. Either way you
must enter a compiler name.
The qlbSupportLib variable is the BQLBxx.LIB file that was sup-
plied with whatever version of the compiler you are using. There
are a few out there for QB 4.0, so double check. QB 4.5 users
should specify BQLB45.LIB. If I remember correctly, BC 6.0 was
supplied with BQLB41.LIB.
Enter any compiler switches you usually use in one string if
needed. Use /FPI/O instead of /FPI <space> /O. If you want to
generate multiple libraries for different compiler switch set-
tings, feel free to run the batch file multiple times, renaming
the resulting .LIB and .QLB file each time. If 123W20.LIB or
123W20.QLB is found in the current directory when the batch file
is started, a reminder is printed to move/delete them and the
batch file terminates.
The batch file will compile the BASIC source code files then add
them to the 123W20.LIB with LIB.EXE. After 123W20.LIB is updated
with the three BASIC object files, LINK is invoked to create the
123W20.QLB QuickLibrary.
6
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
BASIC 7.0+ users should run the MAKEFAR batch file only. It will
generate two .LIB files (123W20.LIB and 123W20F.LIB) and one .QLB
file, 123W20F.QLB. The syntax is:
MAKEFAR compilerName [compiler / options]
The compilerName variable is usually BC, unless you keep various
versions of the compiler on your disk, as I do.
The MAKEFAR batch file will use the /FS and /FPI switches auto-
matically for 123W20F.LIB and the 123W20F.QLB files. Any other
switches can be added except for /FS, but use care when choosing
the switches. The switches need to be entered without any spaces
separating them, use /O/T instead of /O <space> /T.
The batch file will compile the BASIC source code files for near
string use with any switches you supply, then add them to the
123W20.LIB with LIB.EXE.
After 123W20.LIB is updated with the three BASIC near string
object files, the compiler is invoked again with the /FS/FPI
switches, plus any you supply, to update the 123W20F.LIB with the
three BASIC source code files. Lastly, LINK is invoked to create
the 123W20F.QLB from 123W20F.LIB.
If you do not supply any switches for the compiler when invoking
the batch file, you will have:
123W20.LIB Default BASIC options of: /FPI and near strings.
[You should be able to link with /O code also.]
123W20F.LIB Far string, /FPI.
123W20F.QLB Far string, /FPI.
One note concerning arrays in EMS memory, the /EA option with
QBX. I am not sure how the 123-Write routines that access arrays
directly in memory will behave. My belief is that when you pass
the segment and offset of the selected array element to a library
routine, the array should be moved by QBX into addressable memo-
ry, the EMS page frame, if it's out in never-never land somewhere
before the CALL is invoked. Then the segment portion of the
array would be somewhere above A000hex, and a 16K boundary
shouldn't be crossed. There doesn't seem to be any firm documen-
tation on this, so if your machine goes into orbit when a call to
one of the array based routines is called, I'd suggest dropping
the /EA switch, or making the array $STATIC. The arrays used are
small enough that this shouldn't be a hardship.
7
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
Notes for both MAKENEAR and MAKEFAR:
If 123W20.LIB or 123W20.QLB already exist in the current directo-
ry, the batch file will stop and prompt you to remove them. The
reason for this is to allow you to tailor, if needed, the library
files to your working environment.
Take a look at the MAKE????.BAT files and feel free to modify
them if needed to your particular needs. Just don't pass on the
123-Write package with the batch files or the .LIB files modi-
fied, please!
LIB will issue a warning message that the BASIC object files are
not in the library the first time you run MAKENEAR.BAT. The
batch file specifies that the BASIC object files be 'replaced' in
the library file, in case it is run more than once. The warning
message can be ignored. If you rerun the batch file for some
reason (Different compiler switches maybe.) the error message
won't appear, and all will be as it should be.
A note on BASIC PDS 7.0.
If you haven't upgraded to 7.1 yet, please do so. No I don't own
stock in Microsoft, and I do feel $50.00 for what I consider a
bug fix to be outrageous, but 7.1 seems to be a much more stable
product. Also, if you don't need the far string memory space,
near string routines will run faster.
COMPILING with 123WRITE:
When compiling to an .EXE file, it's best to compile from the DOS
command line, NOT from within the QuickBASIC environment. There
is a switch that QB throws into the LINK command line that seems
to cause errors and trashed EXE's. QBX may also mix near and far
string .LIB files if you're not careful.
8
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
Sequence of 123-Write calls:
There can only be one worksheet file open for writing at a time.
The routines need to be called in the proper sequence:
1. Open the file, of course.
Use either:
lFileOpen(filename$, errCode%)
Supply a fully qualified file name with
the drive and path if desired. An existing
file will be over-written.
Or
lFileAppend(filename$, errCode%)
To open an existing worksheet file to add
on to it.
2. Write any column width settings desired.
Use either:
lSetCol(column%,colWidth%)
for a single column width setting.
Or
lSetCols(columnWidths%())
for a variety of columns.
3. Set the cell format for strings and numeric data if other
then the defaults of left-aligned label cells and "default"
numeric cells. See page 12 for default set ups.
4. Set the starting column and row, if other than:
0,0 (A1) if lFileOpen was used or the
{end}{home} cell if lFileAppend was used.
5. Write the data.
6. Close the file.
CALL lClose
Closing the file is a must. If it's not done via a call to
lClose, you'll get a "Part of File Missing" error from Lotus when
loading the spreadsheet. Lotus needs certain information when it
loads a worksheet, and the information can't be written to the
file by 123-Write unless you call the lClose sub-program to close
the file. If a worksheet file is not closed, the disk directory
will not be updated, and a file handle will lost from DOS.
The worksheet file is opened and manipulated by using DOS file
access calls, not BASIC file calls. One important item to remem-
ber is: If you use a CLOSE (all) statement in your program, the
worksheet file WILL NOT be closed. This is just the opposite of
the behavior in earlier versions of 123-Write.
9
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
When the spreadsheet file is opened, either with lFileOpen or
lFileAppend, the lClose routine is logged with BASIC. What this
means is that if you're working in the QuickBASIC environment,
and your program terminates abnormally, lClose will be called
automatically. If you make a change to your program while step-
ping through it, and QuickBASIC prompts that "You'll have to
restart your program", and you elect to restart the program,
lClose will be called before control is passed back to you. What
that means is that you should not try to write to a spreadsheet
file that was opened before you made the change, by assuming it's
still open and stepping 'around' a call to lFileOpen. Check your
BASIC manual for a description of the B_OnExit routine, in the
mixed language programming guide, for a better description of
what this all means.
Another item of note is range names. If your spreadsheet may be
read by a word processor, or manipulated by some other commerical
product, then keep the following in mind.
When Lotus saves a spreadsheet to disk, it writes the spreadsheet
in a consistent manner. A certain amount of data is written at
the start of the file, before the cell contents. As the cells
are written, they are placed in the file in a row by row, left
column to right column sequence (A1, B1, C1, A2, B2, C2, A3, B3
etc). 123-Write writes as much info as needed at the start when
lFileOpen is called, then as calls are made from BASIC, the
specified data is appended to the file. With 123-Write, you can
write almost anything anywhere you'd like in the file. Matter of
fact I think that's a nice feature, and Lotus and some clone
spreadsheet programs don't seem to have a problem with 'anything
goes' style of files, a setting change or a repeated cell just
replaces the previously read data. The problem arises when you
you use a 'viewer' or try to import to a word processor.
I use Wordstar, so I'll use that product as an example. When the
'other' product reads the file, it may assume that the file was
written by Lotus et al. If it doesn't find range names where
Lotus would have placed them, it might assume that the file has
no range names. Wordstar 5.0 behaves this way. When the
123DEMO2.BAS file was written, calls to lRangeName were inter-
spersed throughout hte program. As each 'page' routine was
called, the range would be defined with lRangeName at the end of
the SUB. When Lotus read the file in, no problem. However, when
I asked Wordstar to import the file, the only option WordStar
would give me was to import the whole file. No range names were
presented. Changing the sequence of calls in the demo program
corrected the problem. What I did was to write the "WriteRange-
Names" sub program and moved all the calls into it. Then insert-
ed a call to the new routine immediately after the call to lFile-
Open, before any cells were written to the file. Wordstar now
lists all the PAGE_xx ranges when I specify an import.
10
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
File viewers are another matter. Lotus's Magellan, I've been
told, displays the file as expected, but other viewers don't
display the whole file. I believe this may be because the demo
writes cells in a 'some here, some there' manner, rather then the
row by row, col by col sequence, and the viewer just loses it,
and displays as much as it can figure out.
Last item is the results of the formulas written to the file.
123-Write does not write the result of a formula to the file, as
a spreadsheet would, but just writes the formula. When the file
is loaded, the spreadsheet does a calculation on all cells, and
you never see the zero 'results'. Not true with an import to a
word processor or a file viewer. Don't be alarmed if the result
of all your hard work appears to be nothing but zeros when
'viewed' or imported to a word processor. A File Retrieve, File
Save sequence is all that should be needed to make your spread-
sheet file more conformist.
Bottom line is, I feel the flexibility afforded is more important
then compatibility with products other than spreadsheet programs.
A perfect example is when reading from a file to create a spread-
sheet. You don't know in advance the cells that are going to be
in a range, so you can't stick the range name definition at the
start of the spreadsheet file. 123-Write allows you to define
the range after the imput file is read and written to the spread-
sheet file, once the variable boundaries of the range are known.
11
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
DEFAULT SETTINGS with lFileOpen.
When a worksheet file is opened with lFileOpen, one of two condi-
tions will dictate the default settings of the worksheet. Either
the call is the first one to open a spreadsheet file (lFileOpen
or lFileAppend) in the current program, or a subsequent call to
lFileOpen in the same program:
Setting: First Open: Subsequent:
--------- ----------- -----------
Calc Iterations: 1 1
Calc Mode: Auto Auto
Calc Order: Natural Natural
Global Protection: Off Off
Printing: Formatted Formatted
Left margin: 4 4
Right margin: 76 76
Top Margin: 2 2
Bottom Margin: 2 2
Lines per Page: 66 66
Hidden Columns: None None
Lotus Default:
Cell Format: Default Default Note1
Label alignment: '(left) '(left) Note2
123-Write Default:
Cell Format: Protected-Default Last setting.
Label alignment: '(left) Last setting.
Date Format: dd-mmm-yy (D1) Last setting.
Time Format: hh:mm:ss AM/PM Last setting.
NOTE1: The Lotus "Default" is a unique cell format setting.
What form it takes, and the cells that are marked with
it, is the format selected with the "/Worksheet Global
Format" command. This format can be selected with the
lSetCellForm routine.
NOTE2: The Lotus "Default" label alignment, as used here,
refers only to what alignment text typed into the
worksheet while in Lotus will take on. It is not
changeable with .
The 123-Write formats determine what format 123-Write will speci-
fy for cells it writes. Every cell written has a format speci-
fied. The label alignment 123-Write default can be overridden by
including a label prefix character in the string that is written
to the cell. See lWriteStr for more information.
12
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
DEFAULT SETTINGS with lFileAppend.
The first consideration here has to be the fact that the work-
sheet file is set with the defaults in effect when it was last
written. That could be either the way it was when you wrote it
with 123-Write, or the settings made to it in Lotus 1-2-3, before
it was saved to disk from Lotus.
That fact precludes a list of what settings are in effect as far
as global worksheet defaults. However, the following 123-Write
defaults can be relied on.
"First Append" refers to the first time your program opens a file
(either lFileOpen or lFileAppend) for append, and "subsequent"
refers to opens for appends while your program is still running,
whether it's the same file or a different file:
Setting: First Append: Subsequent:
--------- ----------- -----------
Hidden Columns: None None
123-Write Default:
Cell Format: Protected-Default Last setting.
Label alignment: '(left) Last setting.
Date Format: dd-mmm-yy (D1) Last setting.
Time Format: hh:mm:ss AM/PM Last setting.
What all this boils down to, is that the above "Last Setting"
items are not changed it they've already been set, otherwise they
are set to the default conditions shown.
13
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
SOME GUIDELINES:
Things to remember when coding calls:
All CALLs that take numeric parameters use 2 byte signed inte-
gers, except for lWriteNum, which takes one double precision 8
byte real value, and lConstMath where the constant is also a
double precision 8 byte real variable.
All cell references in Lotus are 0 based. Column A is column 0,
and row 1 is row 0. Clear as mud right? Well, just remember to
subtract one from what you think of the row number as. If you
count columns on your fingers like I do, the first finger is 'B'.
All the routines that need column and row numbers use the Lotus
zero based references. lCol is a function (remember to DECLARE
it as an INTEGER function in your program if you use it.) that
will return the zero based column number when called with a one
or two byte string. See the entry for lCol for an example. It
relieves the strain of counting on your fingers.
Now an important point. The logic in these routines (if any) is
based on a left to right, top to bottom sequence. This simulates
the printing of a report.
Every time a routine is called that writes data to a cell, the
cell pointer is:
Increased by one column if the write is successful, the row
stays the same.
May not be increased if there is an error detected.
May move the pointer after an error is detected, because
the Lotus function @ERR is written to the cell.
Won't move the cell pointer, because there is no cell data
written. lGlobal and lSetCol are examples.
If you should attempt to write a cell beyond the 255th, (IV)
the routines will increment the row and return to column
0 (column A).
Each routines' description will describe what actions are taken
for errors, out of range paramters, etc.
When printing to the printer or screen, you allow for line feeds,
in 123-Write, the lLineFeed routine serves the same purpose. The
row is increased and the column returns to 0.
Another point is when using the 'cursor movement' routines,
lLeft/lRight/lUp/lDown. If you have just written to cell A5 for
example, which is column 0, row 4, and call lUp(4) to move up to
row 0, you will be in column B (1). The cell pointer was moved
one column to the right after A5 was written. Some allowances
14
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
for the column increments upon cell writing have to be calculated
in.
Where Am I in this 'SpreadSheet' anyway?
The answer to that depends upon how the spreadsheet file was
opened. If it's a new spreadsheet file (lFileOpen) then the
column pointer is set to column 0 (A) and the row pointer is set
to row 0 (1) when the file is opened. The {HOME} position there-
fore, is the first cell written, then 1,0 (B1), then 2,0 (C1). A
CALL to lLineFeed at this point would mean that the next cell
written would be 0,1 (A2), then 1,1 (B2) etc. It takes a little
getting used to, but it works well.
If the file was opened with lFileAppend, then the cell pointer
will be set to the {end}{home} position. This cell is the bottom
right corner of the spreadsheet. It may very well contain data,
so the first thing after calling lFileAppend you need to do is
position the cell pointer to where you want the next stream of
data to be written.
There are routines to get the current location of the cell point-
er and to set the location of the cell pointer.
First lGetLoc(whereAmIcol%, whereAmIrow%) returns the current
cell pointer column and row coordinates, and then a call to
lSetLoc(nextCol%, nextRow%) will adjust the cell pointer.
What's new?
This is not meant to be a substitute for reading the entire
manual if you've used previous versions of 123-Write. I have
tried to make version 2 quite a bit more comprehensive, and a lot
of the calling syntax has been changed from version 1.0 and 1.1.
That's why I've changed every routine's name.
Three reported bugs in version 1.0 were corrected in version
1.1a/b, and or 2.0.
The lDate routine choked on a date delimited by slashes. That
was corrected in 1.1a/b so a dash "-" or a slash "/" was OK. New
feature (??) in 2.0, is that the delimiter is ignored altogether!
Calculations are based on the length of the string passed, so a
little more work is on your shoulders. See the entries for both
lDate and lTime for more information.
The second was a MAJOR bug in the 1.0 LotusWriteStr routine. The
code attempted to test the second character of the string passed
to the routine {ie: MID$(data$,2,1)} when a test of the string's
length hadn't verified it was at least two bytes long. A real
easy one, and it shouldn't have been there, but the easy bugs are
the ones you don't (at least I miss them) plan and test for. My
apologies on this one.
15
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
The last one was an annoyance, the string passed to LotusWriteStr
(ver 1.0) was modified if it did not contain a leading label
prefix character. The routine would prefix the passed data
string with the default label prefix character and that string
got returned modified. The lWriteStr routine now manipulates the
string internally and won't modify the string passed to it.
Version 2.0:
At this point I'm so muddled from converting everything over to
assembly language, I'm sure I won't cover all the changes. Off
the top of my head:
lBoolean Sub program added. Allows you to use the Lotus #AND#,
#OR#, #NOT# operators, and equal, not equal, less than
greater than etc.
lCellFunction
Now supports all 92 of the Release 2.2 @functions.
Range, data base and mixed references have been added
to the cell functions.
lColFunc Both routines have had the @STD and @VAR functions
and added. Also, the lColFunction can use @ROWS and the
lRowFunc lRowFunction can use @COLS.
lConstMath
Now allows you to reverse the order of the constant
and cell reference.
lHideCol Added to allow you specify hidden columns.
lLink Added to use the Release 2.2 spreadsheet file linking
feature.
lPrintBorders
Has been modified to allow the one cell 'A1' to be
designated as a border row or border column.
lSetCellForm
Has been revised to allow setting of all possible cell
formats, including +/-, general, hidden and default.
lTimeNow Added to write the @NOW formula to a cell in a time
display format.
16
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
Again, many of the parameters passed to the routines in version
2.0 have been changed, and I have to ask that you scan this
manual and your code carefully for any changes. Finally, version
2.0 of 123-Write is not intended to be backward compatible with
earlier versions. I attempted to keep the calling syntax as
close as possible with 1.0 and 1.1 versions wherever possible.
With the need for additional error information to be passed, I
felt that it would be better to use different routine names
altogether. Therefore, if you're upgrading, please don't attempt
to use a search and replace on the "Lotus" that all the version
1.x routine names used, and leave it at that. Use the 123W20.BI
file and be sure to double check the parameter passing sequences.
Detailed descriptions of each routine follow in alphabetical
order. All routines begin with L---
Error actions are defined at the end of each routine's descrip-
tion.
The changes made to the cell pointer (CP) are listed for each
routine, and in each routine's error action section the action is
defined as:
(CP +) Cell pointer increased to next column.
(CP 0) Cell pointer not increased.
17
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lBoolean
COMPATIBILITY: All releases.
CELL POINTER: Increased 1 column.
SYNTAX: lBoolean (col1%, row1%, col2%, row2%, operation%)
col1% is the column number of the first cell.
row1% is the row number of the first cell.
col2% is the column number of the second cell.
row2% is the row number of the second cell.
operation% indicates the comparison to be performed
between cell 1 and cell 2.
Operation codes are:
1 Equal to Cell1 = Cell2
2 Not equal to Cell1 <> Cell2
3 Less than or equal to Cell1 <= Cell2
4 Greater than or equal to Cell1 >= Cell2
5 Less than Cell1 < Cell2
6 Greater than Cell1 > Cell2
7 AND (not bitwise) Cell1#AND#Cell2
8 OR (not bitwise) Cell1#OR#Cell2
9 NOT (not bitwise) #NOT#Cell1
lBoolean requires two cells to work on, unless the NOT operator
is being used. With the not operator, the first cell coordinates
are used, the second column/row pair is ignored, but still must
be in the call (use 0,0).
Lotus will return a positive 1 in the cell if the condition is
true, a zero otherwise for the comparison set (operations 1
through 6). The AND, OR, and NOT functions are not bitwise
operators, the following rules are used by Lotus:
AND: If both cells are not zero, returns 1 otherwise
returns 0.
OR: If either cell is not zero, returns 1 otherwise
returns 0.
NOT: If the target cell is zero (false) returns 1,
otherwise will return false, 0.
ERRORS:
No file open: No action taken.
Oper% out of range (1-9): @ERR in current cell. (CP +)
18
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lCellFunction
COMPATIBILITY: Various, see table page 21.
CELL POINTER: Increased 1 column.
SYNTAX: lCellFunction(dSeg%, dOfs%)
dSeg% is the VARSEG() of the first element of an
integer array, or a user defined TYPE.
dOfs% is the VARPTR() of the first element of an
integer array, or a user defined TYPE.
lCellFunction writes a specified Lotus @ function to the current
cell. All 92 of release 2.2's @ functions are supported.
Multiple functions can't be combined, the alternative is to write
the components of the multiple functions to out of the way cells,
and operate on them in steps.
The routine expects the far address of an integer array or a user
defined type that is made up of only integers.
The first element indicates the function to perform. With the
exception of the @CHOOSE function, the second through x elements
are pairs of col/row co-ordinates. The function requested deter-
mines what is to follow. Some functions operate on one or two
absolute cells, some on ranges, and some mix ranges and cells.
See the Lotus documentation for the correct sequences of opera-
tors required for each @function.
When using the @CHOOSE function, number 18, the second element
indicates the number of absolute cell references that follow,
then the column, row co-ordinates for each cell.
When using an integer array, DIMed as array%(1 TO 12) for exam-
ple, you could set up a call to write the @IF function to the
current cell as:
array%(1) = 29 'Function 29 is @IF(
array%(2) = 0 'Col A Test cell: A1
array%(3) = 0 'Row 1
array%(4) = 2 'Col C A1 <> 0? : C4
array%(5) = 3 'Row 4
array%(6) = 4 'Col E A1 = 0? : E6)
array%(7) = 5 'Row 6
CALL lCellFunction(VARSEG(array%(1)),VARPTR(array%(1))
Would result in: @IF(A1,C4,E6) in the current worksheet cell.
19
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
lCellFunction (continued)
You could use a TYPE'd variable instead of an array:
TYPE functionType
fCode AS INTEGER
col1 AS INTEGER
row1 AS INTEGER
col2 AS INTEGER
row2 AS INTEGER
col3 AS INTEGER
row3 AS INTEGER
col4 AS INTEGER
row4 AS INTEGER
END TYPE
DIM func AS FunctionType
func.fCode = 85 '85 = @RATE, requires 3 cells
func.col1 = 0 ;Col A
func.row1 = 0 ;Row 1
func.col2 = 2 ;Col C
func.row2 = 3 ;Row 4
func.col3 = 4 ;Col E
func.row3 = 5 ;Row 6
CALL lCellFunction(VARSEG(func),VARPTR(func))
Results in: @RATE(A1,C4,E6) being written to the current work-
sheet cell.
In the table on the following page, each function is listed
alphabetically. The individual 123-Write function number is
listed first, the Lotus @ function, then the number of cells
needed, followed by the type of parameter and Lotus version. The
number of cells varies by parameter type. The parameter types
are variables (cells), ranges, mixed parameters or database
parameters. The parameter type codes are:
V a variable, which is a cell address. Requires an
integer column number then an integer row for each
variable. Maximum is 4, which would need an integer
array of 9 elements (Function #, c1, r1,...c4, r4)
R a range, which is two cell addresses, the upper left
corner cell, then the lower right corner cell. Those
functions that are marked R only require one range
maximum, so a 5 element array will handle them:
Function #, upLeftCol, upLeftRow, lowRcol, lowRrow.
D a database function parameter. These take the form of
input range, offset cell, criteria range, so a 10
element array is needed.
20
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
## @Function Pass Type Ver ## @Function Pass Type Ver
------------------------------------------------------------------
84 = @@ 1 V 20 74 = @LEFT 2 V 20
3 = @ABS 1 V 1a 40 = @LENGTH 1 V 20
15 = @ACOS 1 V 1a 7 = @LN 1 V 1a
14 = @ASIN 1 V 1a 6 = @LOG 1 V 1a
13 = @ATAN 1 V 1a 73 = @LOWER 1 V 20
12 = @ATAN2 2 V 20 54 = @MAX 1 R 1a
51 = @AVG 1 R 1a 43 = @MID 3 V 20
78 = @CELL 3 M* 20 53 = @MIN 1 R 1a
49 = @CELLPOINTER 1 V 20 36 = @MINUTE 1 V 20
44 = @CHAR 1 V 20 17 = @MOD 2 V 1a
18 = @CHOOSE ? V 1a 31 = @MONTH 1 V 1a
80 = @CLEAN 1 V 20 82 = @N 1 R 20
45 = @CODE 1 V 20 1 = @NA 0 1a
69 = @COLS 1 R 20 25 = @NOW/TODAY 0 1a
10 = @COS 1 V 1a 56 = @NPV 3 M* 1a
52 = @COUNT 1 R** 20 8 = @PI 0 1a
87 = @CTERM 3 V 20 26 = @PMT 3 V 1a
24 = @DATE 3 V 1a 77 = @PROPER 1 V 20
47 = @DATEVALUE 1 V 20 27 = @PV 3 V 1a
62 = @DAVG 5 D 1a 23 = @RAND 0 1a
30 = @DAY 1 V 1a 85 = @RATE 3 V 20
63 = @DCOUNT 5 D 1a 71 = @REPEAT 2 V 20
90 = @DDB 4 V 20 76 = @REPLACE 4 V 20
65 = @DMAX 5 D 1a 75 = @RIGHT 2 V 20
64 = @DMIN 5 D 1a 33 = @ROUND 2 V 1a
67 = @DSTD 5 D 1a 70 = @ROWS 1 R 20
61 = @DSUM 5 D 1a 81 = @S 1 R 20
66 = @DVAR 5 D 1a 37 = @SECOND 1 V 20
2 = @ERR 0 1a 9 = @SIN 1 V 1a
83 = @EXACT 2 V 20 88 = @SLN 3 V 20
16 = @EXP 1 V 1a 5 = @SQRT 1 V 1a
21 = @FALSE 0 1a 58 = @STD 1 R 1a
46 = @FIND 3 V 20 42 = @STRING 2 V 20
28 = @FV 3 V 1a 50 = @SUM 1 R 1a
60 = @HLOOKUP 4 M* 20 89 = @SYD 4 V 20
35 = @HOUR 1 V 20 11 = @TAN 1 V 1a
29 = @IF 3 V 1a 86 = @TERM 3 V 20
68 = @INDEX 4 M* 20 34 = @TIME 3 V 20
4 = @INT 1 V 1a 48 = @TIMEVALUE 1 V 20
59 = @IRR 3 M* 1a 79 = @TRIM 1 V 20
91 = @ISAAF 1 V 22 22 = @TRUE 0 1a
92 = @ISAPP 1 V 22 72 = @UPPER 1 V 20
20 = @ISERR 1 V 1a 41 = @VALUE 1 V 20
19 = @ISNA 1 V 1a 57 = @VAR 1 R 1a
38 = @ISNUMBER 1 V 20 55 = @VLOOKUP 4 M* 20
39 = @ISSTRING 1 V 20 32 = @YEAR 1 V 1a
* M = Mixed, See specific format on next page.
** See note next page.
21
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
For the Mixed parameter functions on the previous page:
@HLOOKUP
@VLOOKUP require a cell, range, cell format. 9 elements.
@CELL
@IRR
@NPV require a cell, followed by a range. 7 elements.
@INDEX requires a range, cell, and cell. 9 elements.
@COUNT is coded to expect a range only. Cell lists and the
other optional entries allowed in Lotus are not
supported in 123-Write.
Note that those functions that work on strings should have a
variable that points to a cell containing a string. If the
variable portion of the function is not of the correct type, the
cell to which the function is written will display "ERR" and that
error can propagate through other dependent cells.
For example, @@(cell) returns the value in the cell specified in
"cell". So the cell referred to should have a string cell name,
such as 'A6.
The @CELLPOINTER("cell") and @CELL("cell") functions, expect the
parameter to be something like: CONTENTS, PREFIX, FORMAT etc. So
the cell that is supplied as a parameter should contain the
string: 'CONTENTS or 'PREFIX or 'FORMAT etc.
With Lotus release 2 and greater, it is possible to work with
cells that contain strings. For example, if cell C23 contained
"Hello World" then any cell that had the formula +C23 would also
display "Hello World". Formulas like this in release 1A though,
will not transfer the string, they expect a numeric argument, and
will display 0 as the result. Any string cell in release 1A are
considered to be equal to zero. Look at the @CHOOSE function in
the demo for an example if you write a 1a compatible file.
Don't forget that BASIC can and will move data around in memory.
The segment and offset of a particular data item is not always
static. Don't try to use variables to hold the segment and
offset of a structure for a later call. I'm especially cautious
on this point, and always embed the VARSEG and VARPTR functions
into CALLs that use segment and offset addresses, as in the
examples for this routine.
ERRORS:
No file open: No action taken.
Oper% out of range (1-9): @ERR in current cell. (CP +)
22
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lCellMath
COMPATIBILITY: All releases.
CELL POINTER: Increased 1 column.
SYNTAX: lCellMath(col1%, row1%, col2%, row2%, oper%)
col1% is the column number of the 1st cell the
math is to be performed on.
row1% is the row number of the 1st cell the
math is to be performed on.
col2% is the column number of the 2nd cell the
math is to be performed on.
row2% is the row number of the 2nd cell the
math is to be performed on.
oper% is one of the following:
1 add cell 1 and cell 2.
2 subtract cell 2 from cell 1.
3 multiplication of cells 1 and 2.
4 Division of cell 1 by cell 2.
5 Cell 1 raised to the power of cell 2.
lCellMath provides simple math operations on two cells. Any
function numbers outside the range 1 through 5 will result in the
target cell being tagged with @ERR.
EXAMPLE: col1 = 0 'A
row1 = 2 '3
col2 = 8 'I
row2 = 5 '6
oper = 3 'Multiply
CALL lCellMath(col1%, row1%, col2%, row2%, oper%)
Will multiply A3 by I6 and place the formula in the current cell.
ERRORS:
No file open: No action taken.
Oper% out of range (1-5): @ERR in current cell. (CP +)
23
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lClose
COMPATIBILITY: All releases.
CELL POINTER: N/A
SYNTAX: lClose
Closes the currently open spreadsheet file after writing the
Lotus end of file marker and updating the worksheet range. The
worksheet range is the cell that results from the {END}{HOME} key
presses in Lotus.
A general CLOSE statement in your program will NOT close the
spreadsheet file. This differs from the 1.x versions of 123-
Write which were written in BASIC.
Note that all the routines in this library keep track of the
spreadsheet file handle internally. lClose resets the handle to
0 and all the routines that write to the file naturally check for
a valid handle before an attempt is made to write data to the
file. Calling a routine when there is no file open, or the file
wasn't opened via lFileOpen or lFileAppend, will not cause a
crash because the internal handle is checked. However, if you
fail to close the file with this routine, three things are possi-
ble/probable:
1. Subsequent calls to lFileOpen or lFileAppend will fail
because the internal handle still indicates a file open.
2. The disk's directory will not be updated properly. One file
handle from DOS's pool will not be available to subsequent pro-
grams until the machine is rebooted.
3. Lotus will beep and issue a "Part of file missing." error
when the spreadsheet loads. Definitely not a reassuring happen-
stance.
Very little error checking is done in the 123-Write routines.
It's your job to debug your mainline code so the file is properly
opened and closed. When in the QB or QBX environment, lClose is
called if there is a file open and your program terminates abnor-
mally or you respond "OK" to one of QB's "You will have to re-
start your program after this edit" prompts.
EXAMPLE: CALL lClose
ERRORS:
No file open: No action taken.
24
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lCol
COMPATIBILITY: All releases.
CELL POINTER: Not changed.
SYNTAX: lCol (column$)
column$ is a one or two byte string.
lCol is a FUNCTION, and as such must be declared in your program
as an INTEGER function before any calls are made to it. Other-
wise the compiler will treat it as an array reference.
lCol returns the zero based column number for the corresponding
column$, if column$ is a valid representation of a Lotus column.
It must be between "A" and "IV". If the string is invalid, lCol
will return a value of -1.
The reason lCol is a FUNCTION rather than a sub program, is so
that it can be used in calls that require an integer column
number. You may notice I make liberal use of this function in
the demo program. In reality however, I would use this function
only where a real variable would come into use. The INPUT of a
column letter in the demo is an example. Otherwise as I debug,
I'd stick a stop statement after the function call, get it's
result and use that value in the finished code for speed.
EXAMPLE:
DECLARE FUNCTION lCol%(a$)
.
.
x = lCol ("AB")
' x should be equal to 27. (A-Z) = 26
' + (A-B) = 2
' - Zero Base = -1
' -----
' 27
See examples under lSetLoc and lSetCol also.
NOTE:
I keep a PRINT lCOL(" ") line in the immediate window while
working in QB. That way, when coding, I can just hit F6, fill in
the column, see the result and code the proper number into other
call statements.
ERRORS:
No file open: OK, returns the column number.
Out of range (A-IV): Returns -1 as column number. (CP 0)
25
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lColFunction
COMPATIBILITY: All releases, except @ROWS, ver 2.0+
CELL POINTER: Increased one column.
SYNTAX: lColFunction (startRow%, endRow%, oper%)
startRow% represents the first row to operate on.
endRow% represents the last row to operate on.
oper% is set to one of the following:
1. @SUM the sum of the cells.
2. @AVG the average of the cells.
3. @COUNT the count of cells with
valid data.
4. @MIN the lowest value in the range.
5. @MAX the highest value in the range.
6. @STD the stnd deviation of the range.
7. @VAR the variance of the range.
8. @ROWS the count of the rows, inclusive.
lColFunction is a quick method of writing common columnar func-
tions to a cell, or a series of cells. The routine assumes the
column to operate on is the current column, so all that's needed
in the call is the first row to include and the last row to
include. You can set the cell pointer with lSetLoc if needed.
An example is probably the best way to illustrate. Lets say
you're writing a spreadsheet where the first three rows are
titles and column headings etc. The first valid data row is 3,
and you have 4 columns of data, 0 through 3 (A through D). The
number of rows of data vary based on the database file you're
reading, so you don't have a fixed ending row when the program is
run. Code would look something like this:
FOR i = 1 TO LOF(dataFile%) \ LEN(dataRecord%)
...writing a row loop...
CALL lLineFeed 'Get set for next row.
NEXT i
'after the last data item is written, your row pointer
'is one below the last row of data, and your column
'will be 0 (A) so:
CALL lGetLoc(currentCol%, currentRow%)
endRow% = currentRow% - 1
FOR i = 0 TO 3
CALL lColFunction(3, endRow%, 1) '@SUM
NEXT
ERRORS:
No file open: No action taken.
Oper% out of range (1-8): @ERR in current cell. (CP +)
26
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lConstMath
COMPATIBILITY: All releases.
CELL POINTER: Increased one column.
SYNTAX: lConstMath(col%, row%, constVal#, oper%)
col% is the column number of the cell the math
is to be performed on.
row% is the row number of the cell the math
is to be performed on.
constVal# is a double precision (8 byte real) value
oper% is one of the following:
1 add cell and supplied constant.
2 subtract the constant from cell.
3 multiplication of cell by the constant.
4 division of cell by the constant.
5 Cell raised to the power of constant.
lConstMath writes a simple math calculation using a cell address
and a constant you supply. Note that the constant you pass must
be an 8 byte double precision real value.
Don't confuse the use of the term constant with BASIC's use
(CONST), a constant in the Lotus sense is a hard number that
won't change. See the example below for how the result would look
in Lotus 1-2-3.
If the operation code you supply is a negative number, then the
order is reversed. If you called this routine with an operation
code of -2 for example, the cell you specify would be subtracted
from the constant, instead of the constant subtracted from the
cell.
Operation values outside the range of -1 to -5 and 1 through 5
will result in the target cell being marked @ERR.
EXAMPLE: CALL lConstMath (0, 3, CDBL(5), 4)
will divide the value of cell A2 by 5.
The spreadsheet formula would appear as:
+A2/5
CALL lConstMath (0, 3, CDBL(5), -4)
will divide 5 by the value of cell A2.
The spreadsheet formula would appear as:
5/A2
ERRORS:
No file open: No action taken.
Oper% out of range: @ERR in current cell. (CP +)
27
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lDate
COMPATIBILITY: All releases.
CELL POINTER: Increased one column.
SYNTAX: lDate(dat$)
dat$ is an 8 or 10 byte date string.
Writes the Lotus date value of the date supplied in dat$ to the
current cell. The value is adjusted to conform with the errone-
ous values 1-2-3 uses. (Lotus thinks that there was a February
29th of 1900, which is incorrect, 1900 was not a leap year.) The
date string passed should be between 01-01-1900 and 12-31-2099.
Useful for doing those 30-60-90 day aging schedules everyone
seems to need.
Only two date formats will convert properly, an eight byte
mm?dd?yy or a ten byte mm?dd?yyyy (Basic's) format. The only
conversion that is done is to an 8 byte date is to prefix the
year with a "19" or "20" depending on the date the program is
run. If the date string passed to the routine cannot be convert-
ed to a numeric value, it is written to the cell as a label
instead of a number.
If a default date format has not been set via a call to lSetDate-
Form, the target cell is formatted to display the date in D1
format. (dd-mmm-yy) which requires a column width of roughly 10
characters. See the lSetCol & lSetCols routines for information
on setting column widths.
The lDate routine is in LDATE.BAS, and the actual assembly rou-
tines are in two parts. lDate1 converts a string date to a long
integer value, where 1 equals 01-01-1900. lDate2 does the actual
write of the double precision value to the worksheet file. Yes,
I couldn't figure out how to do the long to 8 byte real conver-
sion in assembly. If you think you'd find use for the lDate1 sub
program in other work, the object file name is LDATES.OBJ for
regular strings and LDATESF.OBJ for far strings. They can be
copied out of their respective library files with LIB.EXE.
EXAMPLE: 'Date stamp the worksheet file:
CALL lWriteStr("As of:", errCode%)
CALL lDate(DATE$)
ERRORS:
No file open: lDate1: Returns date value.
lDate2: No action taken.
Date not 'convertible': lDate1: Returns zero.
lDate2: Writes whatever long
integer is passed to
it with the current
date format.
28
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lDown
COMPATIBILITY: All releases.
CELL POINTER: Increased count% rows.
SYNTAX: lDown(count%)
count% is the number of rows.
lDown is the equivalent of hitting the down arrow key in Lotus.
The next row to be written will be increased by the number of
rows the routine is called with. If your target environment is
Lotus 1-2-3 ver 1a, do not exceed 2047. The maximum row number
is 8195. Attempts to 'cursor' down beyond row 8195 will leave
the cursor in row 8195.
EXAMPLE: move% = 5
CALL lDown(move%)
'The next cell to be written will be 5 below the
'current row, in the same column.
ERRORS:
No file Open: No action taken.
Count% + current row > 8195: Cell pointer row set to 8195.
(Ver 1a note! Maximum worksheet row count in 1a was 2047. lDown
will not mind going beyond it. If you're writing 1a compatible
spreadsheets, keep a seperate count to be sure you don't exceed
2048 rows.)
29
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lFileAppend
COMPATIBILITY: All releases.
CELL POINTER: Set to {END}{HOME} position of file.
SYNTAX: lFileAppend(fileName$, ecode%)
fileName$ is a fully qualified file name, that
may include a drive and path if needed.
ecode% is returned to indicate an error.
-1 A spreadsheet file is already open.
-2 Invalid filespec length.
-3 DOS version < 2.0 (whew!)
-4 The specified file is not a Lotus
123 version 1a, or 2.0+ file.
Positive error codes indicate a DOS
error, and are returned unchanged from
DOS. An error code of 2 indicates "File
Not Found". The error codes in DOS do
not coincide with BASIC error code
values however. See appendix A for some
that I've come across.
Use lFileAppend to open an existing spreadsheet file and add on
to it. If your program is running under DOS 3.0 or higher, then
the file is opened in "Deny All" mode. If a file extension is
not specified (no . dot), then a .WKS extension is assumed.
Lotus files need the {end}{home} cell position written early in
the file, so 123-Write tracks this cell continuously. When
opening a spreadsheet file to append data to it, this routine
will read as much of the existing file to determine the current
{end}{home} cell as needed, then gets ready to write any data you
wish. The updated {end}{home} cell is written to the file when
lClose is called.
The cellpointer is set to the current {end}{home} cell, so the
next cell to be written after lFileAppend is the {end}{home}
cell. The {end}{home} cell will most likely have valid data in
it, and the first thing you should do after a call to this rou-
tine is position the cell pointer with a call to lSetLoc or
lLineFeed before writing any data to the file.
Now a caution, although a minor one. It is OK to open a file,
position the cell pointer to a previously written cell and
'over-write' the data in that cell. The thing to keep in mind
though, is that the cell is not physically over written in the
disk file, but that another entry for that cell is added to the
end of the file. This causes no problems with Lotus, but I'll
throw in an example anyway.
30
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
lFileAppend (continued)
Say you have a worksheet that has your Year to Date Widget sales
in cell 'A5'. Your application can determine the year to date
sales from anywhere of course, and you want it to open the spre-
adsheet and update cell 'A5' whenever it is run. Let's say your
application is run once a week. You open the spreadsheet for
append with this routine, call lSetLoc to set the cell pointer to
'A5' and call lWriteNum to write the year to date widget sales
figure. Then you close the file and go on your way.
Now let's jump ahead to December when the New Year's Bonus sub-
ject comes up, which is based on year to date widget sales.
Amazingly, this is the only time the spreadsheet in question gets
loaded. What will happen, is that Lotus will assign a value to
cell 'A5' the first time it comes across it in the file, then
change the 'A5' value the next time it reads it (your first
update) and continue on, replacing the 'A5' value 52 times. Your
disk file also has 52 entries in it for cell 'A5' which is a
physical consideration. What you will end up with, is the last
year to date widget sales figure in 'A5' which is fine.
Now the reason I think it's not a major consideration is that
when the spreadsheet is saved from within Lotus, only the current
(last) value of 'A5' would be saved. The file size and loading
time would revert back to normal. Few spreadsheets are ever only
read into Lotus and not saved. On the other hand, if there were
a large number of cells being over-written regularly, and the
spreadsheet was only for some type of reference, things may get
out of hand. A hint would be to write a macro in an out of the
way place, named '\0' that would save the file as soon as it's
loaded into 1-2-3. The lRange, cell pointer positioning rou-
tines, lHideCol, and lWriteStr routines will allow you to write
an invisible macro for just this purpose.
ERRORS:
Most (?) will be returned in the errCode% variable.
31
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lFileOpen
COMPATIBILITY: All releases.
CELL POINTER: Set to 0,0 (Cell A1).
SYNTAX: lFileOpen(fileName$, errCode%)
fileName$ is a fully qualified file name, that
may include a drive and path if needed.
errCode% is returned to indicate an error.
-1 A spreadsheet file is already open.
-2 File spec is too long or null.
-3 The current DOS version is less
than version 2.0. (Sad)
Positive error codes indicate a DOS
error, and are returned unchanged from
DOS. The error codes in DOS do not
coincide with BASIC error code values
however. See appendix A for some that
I've come across.
lFileOpen opens a worksheet file for output. Pass the routine a
valid file name which can include a drive and path if needed. If
DOS version 3.0 or higher is in use, the file is opened with a
"Deny All" access code. No error checking or parsing of the file
name (except for an extension) is done. You're in charge here,
and the error codes returned are few. If the file already ex-
ists, it will be over written.
You can pass any file name you wish. If the file name does not
include an extension, ".WKS" is used by default. This routine
writes the initialization data to the spreadsheet file. The
initialization identifies the worksheet file as a version 1a
file, and includes only the data that 1a would write. This does
not seem to pose any problem with a ".WK1" extension. When Lotus
2.0 loads a ".WK1" file with a 1a signature, no errors are
generated, so I assume no extension to file type check is done by
Lotus 2.0 - 2.2.
If there is already an open worksheet file, any calls to the
lFileOpen routine will fail with an errCode of -1 being passed
back. Use lClose to close the spreadsheet file, after which
another call to lFileOpen can be made to open another spreadsheet
file.
EXAMPLE: CALL lFileOpen("D:\LOTUS\TESTFILE.WK1")
will override the default "WKS" extension, and
write the file to drive D, in the sub directory
named LOTUS.
ERRORS: Most (?) will be returned in the errCode% variable.
32
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lGetLoc
COMPATIBILITY: All releases.
CELL POINTER: Unchanged.
SYNTAX: lGetLoc(whatCol%, whatRow%)
whatCol% will be returned equal to the next
column number that is to be written.
whatRow% will be returned equal to the next
row number to be written.
Not a lot to say about this one, returns the row and column
numbers of the next cell to be written. Just remember that the
column and row coordinates are always 0 based, column A = 0 and
row 1 = 0. See also the lSetLoc routine which allows you to set
the row and column for the next write.
EXAMPLE: CALL lGetLocation(currentCol%, currentRow%)
IF (currentRow% + 1) MOD 20 = 0 THEN CALL pageHeader
If you had a routine called pageHeader that you
wanted to call for each 20 row "screen page" this
would do it. (I think.)
ERRORS:
File not open: Variables returned unchanged, no action.
33
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lGlobal
COMPATIBILITY: All releases.
CELL POINTER: Unchanged.
SYNTAX: lGlobal(item%, switch%)
item% is a value from 1 to 5.
switch% adjusts the specified item.
When item% Worksheet global switch%
is: item effected: value:
1 Calculation iterations. 1 through 255 1 *
2 Auto recalculation. 0 = Off (Manual)
otherwise On (Automatic) *
3 Calculation order. 0 = natural *
1 = column-wise
2 = row-wise
4 Unformatted Printing. 0 = Formatted print *
otherwise Unformatted print
5 Global worksheet protection 0 = Unprotected *
otherwise protection On
This routine will allow you to change any of the default global
settings of the worksheet. If the file is not opened via lFi-
leAppend, the defaults for each item are marked with the * aster-
isks above. The qualifier about how the file is opened is added
because these settings could already be set in the file that is
being appended to. To speed processing, the settings above are
not read when lFileAppend is used, however, they can be added to
a file opened in append mode. The last global switch read will
take precedence.
A note is in order regarding protection. As a default, 123-
Write will write each cell as a protected cell, but the protec-
tion won't be enabled unless it is specifically turned on. In
Lotus the /Worksheet Global Protection Enable series of commands
will do it, or in 123-Write a call to this routine. Cell protec-
tion can be disabled for a series of cells by using the lSet-
CellForm routine, which will toggle numeric and string cell
protection off and on. See also lSetDateForm and lSetTimeForm
which have toggles for cell protection also.
ERRORS:
File not open: No action taken.
Invalid item%: No action taken. (CP 0)
34
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME lHideCol
COMPATIBILITY: Release 2.0+
CELL POINTER: No change.
SYNTAX: lHideCol(col%, writeFlag%)
col% is the zero based column number.
writeflag% indicates that the hidden column record
should be written to the spreadsheet
file.
lHidecol can be used to mark any column in the worksheet as a
hidden column. Multiple columns can be hidden with repeated
calls to this routine. Lotus 1-2-3 uses a single record for all
255 columns in the spreadsheet, and 123-Write maintains an inter-
nal record of the columns you mark as hidden. When this routine
is called with the write flag non-zero, the hidden column record
is written to the file.
A check is made of the column number first. If the column number
is less than zero, a check is made of the write flag. If the
write flag is non zero, the hidden column record is written to
the file. This allows you to specify writing the record without
specifying a column to hide (ie: call with -1 and -1).
The 123-Write internal record is not reset until a call is made
to lFileOpen or lFileAppend. This means that if your code is
hiding columns interactively with the user, you could call this
routine with the write flag set each time the user selected a
column. The end result would be that all selected columns are
hidden, but the disk file would be needlessly bloated. A better
method in this situation would be to leave the write flag zero as
columns are marked, then call this routine with the column varia-
ble set to less than zero and the write flag non zero immediately
before calling lClose.
Because 123-Write doesn't allow nested functions and formulas,
the ability to hide a column is very handy. You could place the
needed comparison data and constant data into cells that are
hidden, and preserve an uncluttered appearance in the resulting
spreadsheet.
ERRORS:
No file open: No action taken.
Column <0 or >255: Write flag checked. If flag is non-zero,
the record is written.
35
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lLeft
COMPATIBILITY: All releases.
CELL POINTER: Column is decreased count% times.
SYNTAX: lLeft(count%)
count% is the number of columns.
lLeft is the equivalent of hitting the left arrow key in Lotus.
The next col to be written will be decreased by the number of
columns the routine is called with. Attempts to cursor left
beyond column 0 will leave the cursor in column 0 (A).
EXAMPLE: move% = 5
CALL lLeft(move%)
'The next cell to be written will be 5 to the
'left of the current cell, in the same row.
ERRORS:
No file open: No action taken.
current column - count% < 0: Column set to zero.
36
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lLineFeed
COMPATIBILITY: All Releases.
CELL POINTER: Col set to zero, Row incremented by 1.
SYNTAX: lLineFeed
When called, resets the internal column pointer to 0, (Column A)
and increases the row pointer by one. The next cell to be writ-
ten will one row lower in column A. If an attempt is made to
write past row 8195, the cell pointer will stay in row 8195.
See lColFunction for an example of using lLineFeed.
ERRORS:
No file open: No action taken.
Current row=8191: Row remains at 8191. Column set to zero.
37
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lLink
COMPATIBILITY: Release 2.2+
CELL POINTER: Column increamented by one.
SYNTAX: lLink(fileSpecRange$)
fileSpecRange$ is a properly formatted linked cell
reference. It must be in the:
<<fileSpec>>RangeName
format.
lLink is intended to be used only with spreadsheet files that are
targeted to be used by Lotus 1-2-3 Release 2.2 or 3.0. Spread-
sheet linking was not available in earlier releases.
To keep the assembly language code as small and tight as possi-
ble, I did not add strong type checking to the string you pass.
Therefore, the string passed to this routine must be exactly as
it would be typed into a cell in 1-2-3. Two left angle brackets,
the full drive and path (if needed) the spreadsheet file name,
two right angle brackets and the cell (not recommended) or range
name to link in.
Care should be taken to use a range name in the spreadsheet that
contains the data to be read into the current worksheet. If a
cell reference is made to a linked worksheet, and that worksheet
is modified (a row inserted for example) the data read into the
current worksheet will be erroneous. By using range names, no
matter how the referenced worksheet is changed, the result will
be correct. I know, the range could be deleted from the refer-
enced worksheet, but that's life.
The range name to reference should be early in the file if it's a
file written by 123-Write. If the range is buried in the file,
intstead of being written right after the lFileOpen call, the
link may fail. Single cell ranges seem to work more reliably.
Remember that 123-Write does not calculate any formulas or func-
tions. When 123-Write writes a cell to a spreadsheet file, if
it's a formula, calculation or function, the result is written as
zero. Lotus calculates all formulas and functions when it reads
in the worksheet, so I elected to take the quick way out. Why
calculate something twice? Especially something like a four
quadrant arctangent! Therefore if you want to link in a cell
from a spreadsheet that was written by 123-Write, the result may
be a zero. The best way around this is to have a test cell in an
out of the way place, that does a comparison for zero on some
part of the linked spreadsheet. See the next page for an exam-
ple.
38
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
lLink (continued)
For example if you use the Lotus @TRUE function in a hidden cell
of a reference sheet written by 123-Write, and name that cell
TRUE_RANGE, then in the current worksheet include a cell that
links to it, let's say in cell AA1, like:
AA1: <<D:\123\123WRITE.WK1>>TRUE_RANGE
Then in AA2, place the link to the data you need for the current
worksheet. Let's assume it's in 123WRITE.WK1 in a cell range
named "Result"
AA2: <<D:\123\123WRITE.WK1>>RESULT
Then you could place the function @NA (the value NA, in Lotus-
Speak) in cell AA3:
AA3: @NA
So now we have everything pretty well set. Now, the value of the
cell: RESULT in: 123WRITE.WK1 is needed in the "main" worksheet
at cell C3. Using the lCellFunction routine to write an @IF
formula, you could write:
C3: @IF(AA1,AA2,AA3)
The result of all this is, is a flag to tell you if the target
worksheet file has been loaded into and saved from Lotus.
If 123-Write had written 123WRITE.WK1 then the result of the
@TRUE function in TRUE_RANGE would be zero, and the current
worksheet would compare that zero via cell AA1 and use the value
NA in cell C3. If Lotus had loaded the 123WRITE.WK1 file, and it
had been saved, @TRUE in TRUE_RANGE would be 1, cell AA1 in the
current worksheet would be 1, and cell C3 would equal the value
read into AA2, which is the calculated result in the RESULT
range.
Clear as mud right? It sounds worse than it is, in reality it is
really simple to implement from 123-Write or Lotus.
ERRORS:
File not open: No action taken.
Null String: No action taken. (CP 0)
String > 240 bytes: No action taken. (CP 0)
39
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lMultiCellMath
COMPATIBILITY: All Releases.
CELL POINTER: Column increamented by one.
SYNTAX: lMultiCellMath(dSeg%, dOfs%)
dSeg% is the VARSEG() of a single dimension integer
array with the column and row coordinates and
the operation to be performed.
dOfs% is the VARPTR() to the array.
This routine is going to take some getting used to. It's purpose
is to write calculations to the current cell using multiple cell
references. (There is a limit to the number of referenced cells,
roughly 341(?) cells but I wouldn't want to try and find the
maximum.)
The integers in the array are read and acted upon sequentially.
Each cell operation requires three elements, the column number,
the row number and what to do with it, the operation code. The
routine starts reading from the specified element in the array,
so be sure to put the first column reference in the element you
use as an argument to the VARSEG and VARPTR functions. The end
of the formula is indicated by an operation code of zero after
the last cell coordinates. DO NOT assume that the upper bound of
the array is checked, it is NOT. If the last operation code in
the array is not a zero, this routine will continue reading
forever, if it doesn't happen to hit a zero in the third "set".
The operation codes, the third element of each set, are:
0. end of calculation.
1. add next cell.
2. subtract next cell.
3. multiply by the next cell.
4. divide by the next cell.
Again, an example might be the best way to illustrate. A work-
sheet with 4 columns (0-3, A-D) of data. We want to take column
A's (0) value, add column B's (1) value to it, and subtract the
value in column D (3), placing the result in column E (4) for
each row.
See the next page for the code example.
ERRORS:
File not open: No action. (CP 0)
Actual 3rd integer not (1-5): La La land. (CP ???)
A element, multiple of 3
not in range (0-5): Terminates function. (CP +)
40
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
LMultiCellMath (continued) Example 1.
DIM Array%(1 TO 9)
.
worksheet file is opened etc...
.
startRow% = 3 'The operations and columns
endRow% = 30 'are constant, so set them:
Array%(1) = 0 'Element '1' col number (A)
Array%(3) = 1 'Element '3' operation: Add next
Array%(4) = 1 'Element '1' col number (B)
Array%(6) = 2 'Element '3' operation: Sub next
Array%(7) = 3 'Element '1' col number (D)
Array%(9) = 0 'Element '3' operation: End calc
FOR i% = startRow% TO endRow%
.
.
write the row data,
columns 0 to 3 (A - D)
.
.
Array%(2) = i% 'The row numbers are going
Array%(5) = i% 'to change to reflect the
Array%(8) = i% 'row we're in.
'
'Then write the formula to column E:
'
CALL lMultiCellMath(VARSEG(Array%(1)),VARPTR(Array%(1))
CALL lLineFeed
NEXT i%
Again note that BASIC can move data around in memory, and it is
important not to assume that an array's (or structure's) address
in memory will stay the same. My examples show the VARSEG() and
VARPTR() functions embedded in the calls because I don't like to
take any chances. Just a chicken at heart I guess.
Also note that a structure could be used instead of an array,
just be sure the "third element = 0" rule is observed. See the
next page for a coding example that duplicates the above, but is
a little easier to comprehend.
41
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
LMultiCellMath (continued) Example 2.
TYPE MultiMathStructure
col AS INTEGER
row AS INTEGER
opr AS INTEGER
END TYPE
DIM multiMath(1 to 3) AS MultiMathStructure
.
worksheet file is opened etc...
.
startRow% = 3 'The operations and columns
endRow% = 30 'are constant, so set them:
multiMath(1).col = 0 'Cell '1' col number (A)
multiMath(1).opr = 1 'Cell '1' operation: Add next
multiMath(2).col = 1 'Cell '2' col number (B)
multiMath(2).opr = 2 'Cell '2' operation: Sub next
multiMath(3).col = 3 'Cell '3' col number (D)
multiMath(3).opr = 0 'Cell '3' operation: End calc
FOR i% = startRow% TO endRow%
.
.
write the row data,
columns 0 to 3 (A - D)
.
.
multiMath(1).row = i% 'The row numbers are going
multiMath(2).row = i% 'to change to reflect the
multiMath(3).row = i% 'row we're in.
'
'Then write the formula to column E:
'
CALL lMultiCellMath(VARSEG(multiMath(1)),VARPTR(multiMath(1))
CALL lLineFeed
NEXT i%
42
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lPrintBorders
COMPATIBILITY: All Releases.
CELL POINTER: No change.
SYNTAX:
lPrintBorders(rulc%,rulr%,rlrc%,rlrr%,culc%,culr%,clrc%,clrr%)
rulc% is the Border Row upper left column
rulr% is the Border Row upper left row
rlrc% is the Border Row lower right column
rlrr% is the Border Row lower right row
culc% is the Border Column upper left column
culr% is the Border Column upper left row
clrc% is the Border Column lower right column
clrr% is the Border Column lower right row
Yes, eight parameters for one call. Lotus 1-2-3 requires all
eight in one record, consequently it has to be written to the
file as one record.
Lotus uses a unique method to indicate an undefined range which
is also used internally by 123-Write. It would be cumbersome to
have to call this routine with the proper values, so I wrote a
test into the code for this function:
If the sum of all the range values (either the column range or
the row range) are less than zero, the range is not defined.
When you define a column or row print border in Lotus, it is
acceptable to define one cell as the 'column' or one cell as the
'row', you can do that in 123-Write also. For instance, to
define column A as the column border and row 1 as the row border,
just call lPrintBorders with eight zeros.
EXAMPLES:
'Set only border rows of rows 1 through 4:
CALL lPrintBorders (0, 0, 0, 3, -1, 0, 0, 0)
'The column range above is less than 0: not defined.
'Set only border columns of columns A and B:
CALL lPrintBorders (-1, -1, -1, -1, 0, 0, 1, 0)
'The row range above is less than 0: not defined.
'Set both the above as the borders:
CALL lPrintBorders (0, 0, 0, 3, 0, 0, 1, 0)
'the border rows are 1 through 4 and columns are A & B.
ERRORS:
File not open: No action taken.
Illegal column or
Illegal row number: Written to the file. No check is made.
43
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lPrintMargins
COMPATIBILITY: All Releases.
CELL POINTER: No change.
SYNTAX: lPrintMargins(lm%, rm%, tm%, bm%, pageLen%)
lm% is the left margin
rm% is the right margin
tm% is the top margin
bm% is the bottom margin
pageLen% is the page length
lPrintMargins sets the printer margins used by Lotus for subse-
quent printing. When 123-Write opens (rather than appends to) a
worksheet file, these variables are set to the default values of:
Left = 4
Right = 76
Top = 2
Bottom = 2
Page = 66
If needed, the default, or previously set (if the file was opened
for append) margins can be changed by calling this routine. NOTE
that all the variables will be written to the worksheet file. If
you want to change just the right margin for example, you must
still supply all the other parameters. Lotus requires all mar-
gins be set in one record.
EXAMPLE:
lm% = 10
rm% = 130
tm% = 2
bm% = 2
page% = 66
CALL lPrintMargins(lm%, rm%, tm%, bm%, page%)
'In effect just changes the left and right print margins.
ERRORS:
No file open: No action taken.
Illegal column or
Illegal row number: Written to the file. No check is made.
44
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lPrintRange
COMPATIBILITY: All Releases.
CELL POINTER: No change.
SYNTAX: lPrintRange (ulc%, ulr%, lrc%, lrr%)
ulc% is the upper left column of the range.
ulr% is the upper left row of the range.
lrc% is the lower right column of the range.
lrr% is the lower right row of the range.
lPrintRange sets up the default print range for the worksheet.
This allows you to predetermine the range, and then you can just
add a macro to print it when Alt-P is pressed. Saves time in
macro writing if the range is preset, by eliminating all the
range input needed to set up the print range. The example
below shows how it can be done. If you use the lGetLoc routine
and save the bottom-most row and right-most col to variables,
then set the print range, it allows you to always define the full
range of cells for printing regardless of the size of the work-
sheet.
EXAMPLE:
'Let's assume all the data has been written
'to the spreadsheet, and that the upper left
'corner of the print range is 0, 0 (A1) and the
'lower right corner is in the variables lrc% and
'lrr%
CALL lPrintRange (0, 0, lrc%, lrr%)
column% = lCol ("Z")
CALL lSetLoc (column%, 0) 'Macro in Z1
CALL lRange("\P", column%, 0, column%, 0) 'Name it
CALL lWriteStr("/PPAGQ", errCode%) '1 cell macro
'The Alt-P macro is just a PRINT, PRINTER, ALIGN,
' GO, QUIT sequence.
ERRORS:
No file open: No action taken.
Illegal column or
Illegal row number: Written to the file. No check is made.
45
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lPrintSetup
COMPATIBILITY: All Releases.
CELL POINTER: No change.
SYNTAX: lPrintSetup (setup$)
setup$ is the Lotus style print control string.
Assigns a printer setup string to the worksheet. The string you
pass to this routine is used exactly as received. An exception
is that only the first 40 characters are used if the string is
over 40 characters long. Remember to use the back-slash (\)
character to start and three digit ASCII numbers to define the
setup string.
EXAMPLE:
'Setting my printer into near letter quality mode
'requires an ESC-W-1 string. ASCII 27, 88, 49
setup$ = "\027\088\049"
CALL lPrintSetup (setup$)
ERRORS:
No file open: No action taken.
Null String: No action taken. (CP 0)
String > 39 bytes. Truncated to 39 bytes. (CP +)
46
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lPrintTitle
COMPATIBILITY: All Releases.
CELL POINTER: No change.
SYNTAX: lPrintTitle (title$, header%)
title$ is the string to assign to the print output.
header% is a flag to indicate a title (non-zero)
or a footer (zero)
When called, sets up either the print title, if header% is non-
zero, or the print footer if header% is zero. The string passed
to this routine is truncated if over 240 characters.
The special formatting characters should be included in the
string passed to this routine. The | character can be used to
break the line into three separate text blocks, each aligned on
the page differently. The @ character will be replaced by the
current system time when the print command is issued. The third
format character is # which indicates page numbering. The page
number is only reset to 1 when the /Print command is invoked.
EXAMPLE:
header$ = "|Income Statement"
footer$ = "|2nd Quarter 1989"
CALL lPrintTitle (header$, -1)
CALL lPrintTitle (footer$, 0)
'Both the header and footer above will be centered,
'There is no 'Left' block.
header$="Income Statement||Page #"
'Will result in a left aligned title, with the page
'number right aligned for instance.
ERRORS:
No file open: No action taken.
Null String: No action taken. (CP 0)
String > 240 bytes. Truncated to 240 bytes. (CP +)
47
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lRange
COMPATIBILITY: All Releases.
CELL POINTER: No change.
SYNTAX: lRange(rangeName$, ulc%, ulr%, lrc%, lrr%)
rangeName$ is the name to assign.
ulc% is the upper left column of the range.
ulr% is the upper left row of the range.
lrc% is the lower right column of the range.
lrr% is the lower right row of the range.
lRange assigns a name of up to 15 characters to the specified
range. Any characters over 15 are ignored. The range name
supplied is NOT parsed to replace any invalid characters. The
name passed to the routine remains unaltered.
Range names are a great time saver in Lotus. When writing a
spreadsheet via 123-Write, you can't be sure that a user won't
move data around after you've written the file. This is a prob-
lem when writing macros especially, if you "hard code" a cell
reference in a text string, it will always refer to that cell, no
matter where the actual data you want to work with is. By using
macros that have range names in them exclusively, the end user
can move everything around, yet your macros will still work
correctly.
As mentioned at the start of this document, if possible, the
range names should be written early in the file.
EXAMPLE:
CALL lRange("Income", lCol("B"), 0, lCol("D"), 19)
Assigns the name INCOME to the range from B1 through D20.
ERRORS:
No file open: No action taken.
Null String: No action taken. (CP 0)
String > 15 bytes. Truncated to 15 bytes. (CP +)
48
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lRight
COMPATIBILITY: All Releases.
CELL POINTER: Column incremented by count%. No change to row.
SYNTAX: lRight(count%)
count% is the number of columns.
lRight is the equivalent of hitting the right arrow key in Lotus.
The next column to be written will be increased by the number of
columns the routine is called with. The maximum column number is
255 (IV). Attempts to 'cursor' right beyond column 255 will
leave the cursor in column 255.
EXAMPLE: move% = 5
CALL lRight(move%)
'The next cell to be written will be 5 columns
'to the right of the current column, in the
'same row.
ERRORS:
File not open: No action taken.
Current column + count > 255: Column set to 255 (IV).
49
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lRowFunction
COMPATIBILITY: All releases, except @COLS, ver 2.0+
CELL POINTER: Increased one column.
SYNTAX: lRowFunction(startCol%, endCol%, oper%)
startCol% represents the first col to operate on.
endCol% represents the last col to operate on.
oper% is set to one of the following:
1. @SUM the sum of the cells.
2. @AVG the average of the cells.
3. @COUNT the count of cells with
valid data.
4. @MIN the lowest value in the range.
5. @MAX the highest value in the range.
6. @STD the stnd deviation of the range.
7. @VAR the variance of the range.
8. @COLS the count of the cols inclusive.
lRowFunction is a quick method of writing common row functions to
a cell, or a series of cells. The routine assumes the row to
operate on is the current row, so all that's needed in the call
is the first column to include and the last column to include.
To illustrate, lets say you're writing a spreadsheet where you
want the sum (@SUM) of the first four columns (A-D) in column E
of each row:
FOR i% = 1 TO LOF(dataFile%) \ LEN(dataRecord%)
.
...writing a row loop...
.
CALL lRowFunction(0, 3, 1) '@SUM the row.
CALL lLineFeed 'Get set for next row.
NEXT i%
ERRORS:
File not open: No action taken.
oper% <1 or >8: @ERR written to current cell. (CP +)
50
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetCellForm
COMPATIBILITY: All releases, except as noted below.
CELL POINTER: No change.
SYNTAX: lSetCellForm(formatCode%, decimals%)
formatCode% Indicates the desired format for numeric
and string cells written after this
routine is called.
decimals% Usually the number of decimal places you
want displayed in subsequent cells.
FormatCode% decimals%
----------- ---------
1 Fixed number of decimal places. 1 thru 15
2 Scientific notation. 1 thru 15
3 Currency format. 1 thru 15
4 Percent format. 1 thru 15
5 Comma format. 1 thru 15
6 Special format (see below)
7 Set cell protection. 0=Off else On
Sets the default cell display for subsequent numeric cells.
Default string alignment can be changed, see lSetStrForm.
For the Fixed, Scientific, Currency, Percent and comma formats,
the decimals% parameter represents the number of decimal places
to display, from 0 through 15.
For the protection option, the decimals% is used as a flag. If
it is zero, cell protection will be turned off. Any non-zero
value will turn on cell protection. Note that if you write
unprotected cells, 1-2-3 will highlight them when the spreadsheet
is loaded.
For the Special option, number 6, the following formats are set,
based upon the value of decimals%:
value Result: value result:
----- ------- ----- -------
0 + / - format. 9 Date, Int'l 1 (2.0+)
1 General 10 Date, Int'l 2 (2.0+)
2 Date 1: dd-mmm-yyyy 11 Time, Int'l 1 (2.0+)
3 Date 2: dd-mmm 12 Time, Int'l 2 (2.0+)
4 Date 3: mmm-yy 13--15 Default (Cell takes
5 Text whatever format is set
6 Hidden (2.0+) by /Worksheet Global
7 Time 1: hh:mm:ss AM/PM (2.0+) Format command.
8 Time 2: hh:mm AM/PM (2.0+)
51
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
lSetCellForm (continued)
Individual cells, though marked as protected cells aren't "write
protected" unless worksheet protection is enabled. Default
worksheet protection is disabled, so a call to lGlobal is needed
to "write protect" protected cells.
When a worksheet file is first opened, if no other formats have
been set (from a previously completed file, for example) the
format string is set to: Protected, Special, Default by 123-
Write.
EXAMPLES: CALL lSetCellForm(1,3) 'result: ####.###
CALL lSetCellForm(4,2) 'result: #,###.##
CALL lSetCellForm(3,2) 'result: $#,###.##
CALL lSetCellForm(7,-1)
'results in cell protection being turned on.
ERRORS:
File not open: Cell format set. (CP 0)
formatCode% <1 or >7: No action taken. Last (or default)
is still in effect. (CP 0)
52
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetCol
COMPATIBILITY: All releases.
CELL POINTER: No change.
SYNTAX: lSetCol(col%, colWidth%)
col% is the column number to set,
colWidth% is the width for the column.
Sets the column width for an individual column. See lSetCols if
you want to set a range of columns at one time.
The Lotus default column width is 9 characters wide. You can set
the width of any column to another value by calling this routine.
The valid widths are 1 to 240 maximum.
EXAMPLE: CALL lSetCol(3, 20)
CALL lSetCol(lCol("D"),20)
both set column D's width to 20 characters.
ERRORS:
File not open: No action taken.
column%<0 or >255: Set to low byte value. (CP 0)
colWidth% <0 or >240: Set to low byte value. (CP 0)
53
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetCols
COMPATIBILITY: All releases.
CELL POINTER: No change.
SYNTAX: lSetCols(colWidths%())
colWidths%() is a single dimension integer array, the
element number indicates the column, the
content of the element indicates the
width for the column.
lSetCols is for setting multiple columns to different widths with
one call. Whether you use this routine or set the column widths
individually with lSetCol is your call. lSetCols is written in
BASIC, the source file being LSETCOLS.BAS. lSetCols calls lSet-
Col using the LBOUND and UBOUND of the passed array as a loop
control, so the columns have to be within one range.
The integer array holds the width for each column. The position
in the array determines the column. If you want to use this
routine to set column zero, there must be a zero element in the
array passed. No OPTION BASE 1 statement in this case, or use a
DIM array%(0 to whatever%) statement to over-ride the OPTION BASE
statement.
With QuickBASIC 4.0+ and BASCOM 6.0+ it is possible to set lower
array bounds of other then 0 or 1, which is handy with this
routine. If you want to set columns 9 through 17 for example,
you can:
REDIM temp%(9 TO 17)
...set elements 9 through 17 to desired widths...
CALL lSetCols(temp%())
EXAMPLE: See above.
ERRORS:
A lower bound less then 0 will result in NO column width data at
all being written. (CP 0)
If the width for a column (element) is less than 1 or greater
than 240 that column is skipped, and it's width is left at the
default (or other) setting of 9. (CP 0)
File not open: No action.
54
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetDateForm
COMPATIBILITY: See below.
CELL POINTER: No change.
SYNTAX: lSetDateForm (format%, protect%)
format% is the desired format number:
1 dd-mmm-yy 16-Feb-53
2 dd-mmm 16-Feb
3 mmm-yy Feb-53
(1-2-3 ver 2.0+ only) 4 mm-dd-yy 02-16-53
(1-2-3 ver 2.0+ only) 5 mm-dd 02-16
protect% is a flag to turn date cell protection
on (non-zero) or off (zero).
Setting the date format is optional, it will default to format 1,
the dd-mmm-yy arrangement when the worksheet is opened with
lFileOpen. The date format you set here will be used with subse-
quent calls to the lDate and lToday routines, numeric and string
cells are left unchanged.
Note that this is a change from version 1.0, where the date
format on calls to lDate was always format 1. All five formats
are available in Lotus 1-2-3 version 2.0 and greater. Only
formats 1, 2 and 3 are recognized by version 1a of Lotus.
A date format can also be specified by calling lSetCellForm as
in version 1.0. However, lSetForm causes all numeric cells
written after it is called to be displayed with the specified
format.
This routine allows you to cut your code down by setting both a
date format and a standard cell format and not being forced into
the default dd-mmm-yy setting.
EXAMPLE:
'Assume you're reading and writing 10 field records where
'the 5th and 7th fields are dates (all others numeric):
CALL lSetDateForm(4,1) 'Lotus D4, protected cell
FOR i = 1 TO 10
INPUT #inputFile, rec$
IF i = 5 OR i = 7 THEN
CALL lDate(rec$)
ELSE
a# = VAL(rec$)
CALL lWriteNum(a#)
END IF
NEXT
ERRORS:
File not open: Date format selection is set. (CP 0)
format%<1 or >5: No action taken. Last (or default)
remains in effect. (CP 0)
55
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetLoc
COMPATIBILITY: All releases.
CELL POINTER: Set to parameters.
SYNTAX: lSetLoc(newCol%, newRow%)
newCol% is the next column you want written,
newRow% is the next row to write.
Use lSetLoc to position the internal column and row pointers to
any cell (within 255 columns and 2048 or 8192 rows) that you want
to write to next. Column and row ranges are checked, use a maxi-
mum row of 2047 for 1-2-3 release 1a, 8191 for later versions.
Only thing to mention (again?) is that the column and row coordi-
nates are 0 based, but at least with the lCol function, we can
get a little help:
Example:
CALL lSetLoc(lCol("C"), 6)
Will result in the cell pointer being set to write to 'C7' next.
ERRORS:
File not open: No action taken.
Col<0 or >255: No action taken. (CP 0)
Row<0 or >8191: No action taken. (CP 0)
56
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetStrForm
COMPATIBILITY: All releases except number 5, 2.0+ only.
CELL POINTER: Not changed.
SYNTAX: lSetStrForm(formatCode%)
formatCode% is any one of the following:
1 Left justify text. ' (Default)
2 Center text in the cell: ^
3 Right justify text. "
4 Repeating character: \
5 The non-print character: | (2.0+ only)
lSetStrForm sets the DEFAULT label alignment character for all
strings written to the file (after this routine is called), if a
label prefix character is not the first character in the string.
See the lWriteStr routine for more information on writing string
data.
If the format code you pass to this routine is not one of the
five available, the last label prefix character selected, or the
default single quote (') is selected.
The single quote (left justify) is the default label prefix
character selected when the spreadsheet file is opened via lFile-
Open or lFileAppend, unless a previous label prefix character has
been defined during the same session.
The cell protection can be set to on or off by the lCellForm rou-
tine.
ERRORS:
File not open: String cell format byte set as above.
formatCode<1 or >5: No action, last seeting stays. (CP 0)
57
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSetTimeForm
COMPATIBILITY: Release 2.0+ only.
CELL POINTER: Not changed.
SYNTAX: lSetTimeForm (format%, protect%)
format% is the desired format number:
(am/pm indicator) 1 hh:mm:ss(am/pm) 09:33:45pm
(am/pm indicator) 2 hh:mm(am/pm) 09:33pm
(24 hour clock) 3 hh:mm:ss 21:33:45
(24 hour clock) 4 hh:mm 21:33
protect% is a flag to toggle cell protection
on (non-zero) or off (zero).
Setting the time format is optional, it will default to format 1,
the hh:mm:ss(am/pm) arrangement when the worksheet is opened.
The time format you set here will be used with subsequent calls
to lTime. Note that time formats (and functions) were not in-
cluded with Lotus until release 2.0.
EXAMPLE:
An example here would be the same as given with the lSetDateForm
routine, and since they're both so similar, I'll refer you to
that routine for an example.
ERRORS:
No file open: Time format set. (CP 0)
format<1 or >4: No action taken, last setting stays.(CP 0)
58
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSortKey
COMPATIBILITY: All releases.
CELL POINTER: Not changed.
SYNTAX: lSortKey (primry%, ulc%, ulr%, lrc%, lrr%, ascnd%)
primry% indicates whether to set the primary sort key
(non-zero) or the secondary sort key (zero).
ulc% is the upper left column of the key range.
ulr% is the upper left row of the key range.
lrc% is the lower right column of the key range.
lrr% is the lower right row of the key range.
ascnd% sets the sort order, ascending (non-zero),
or descending (zero).
If a sort range has been set, the primary sort key and the sec-
ondary sort key can be set via this routine.
As in lPrintRange, this routine can alleviate the need for long
macros. If the range of data to manipulate is known, it can all
be set up via 123-Write, and a much simpler macro can be written,
one that doesn't need to determine boundaries etc.
Note that the key ranges must be within the data sort range.
EXAMPLE:
CALL lSortKey (-1, 0, 0, 0, endRow%, -1)
'Sets the primary sort key to cell A1 to cell Ax
'where x is a variable row number.
ERRORS:
File not open: No action taken (CP 0)
Invalid cells: Blissfully written to file. (CP 0)
59
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lSortRange
COMPATIBILITY: All releases.
CELL POINTER: Not changed.
SYNTAX: lSortRange(ulc%, ulr%, lrc%, lrr%)
ulc% is the upper left column of the sort range.
ulr% is the upper left row of the sort range.
lrc% is the lower right column of the sort range.
lrr% is the lower right row of the sort range.
lSortRange sets up the default sort range for the worksheet.
This allows you to predetermine the range, and then you can just
add a macro to print it when Alt-S is pressed. Saves time in
macro writing if the range is preset, by eliminating all the
range input needed to set up the print range. The example
below shows how it can be done. If you use the lGetLoc routine
and save the bottom-most row and right-most column to variables,
then set the sort range, it allows you to always define the full
range of cells for sorting regardless of the size of the work-
sheet.
EXAMPLE:
'Let's assume all the data has been written
'to the spreadsheet, and that the upper left
'corner of the sort range is 0, 0 (A1) and the
'lower right corner is in the variables lrc% and
'lrr%
CALL lSortRange (0, 0, lrc%, lrr%)
column% = lCol ("AA")
CALL lSetLoc (column%, 0) 'Macro in AA1
CALL lRange("\S", column%, 0, column%, 0) 'Name it
CALL lWriteStr("/DSGQ") '1 cell macro
'The Alt-S macro is just a DATA, SORT, GO, QUIT sequence.
ERRORS:
File not open: No action taken.
Invalid cells: Happily written to the file.
60
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lTime
COMPATIBILITY: Release 2.0+ only.
CELL POINTER: Advanced to next column.
SYNTAX: lTime(timeStr$)
timeStr$ is a valid string representation of a
legitimate time. (What?)
The expected format is: hh:mm:ss or hh:mm
Don't really know how much use there may be for this routine, but
since I included the numeric date handling capability, I figured
I should also include time handling.
No extended checking is done with the string you pass to this
routine. If it isn't eight characters (hh:mm:ss) or five charac-
ters (hh:mm) it writes the string to the file instead of the
numeric value of the time. The routine is coded to use the
format set with lSetTimeForm, and if there isn't a format set, it
uses time format 1, Lotus version 2.0+ will display it in:
hh:mm:ss AM
or
hh:mm:ss PM
The cell contents are a fraction, indicating the fraction of a
day that has passed. The lTime sub-program, written in BASIC is
contained in the LTIME.BAS file. The assembly language routine
lTime1 returns the number of seconds since midnight for a given
time. (ala BASIC's TIMER function.) Lotus interprets time
values based on the percentage of a day. For example .25 is 6:00
AM, .50 is 12:00 Noon. The lTime sub divides the seconds since
midnight by the number of seconds in a day, and lTime2 writes the
result to the spreadsheet file in the currently set time format.
>> NOTE <<
Time values were not introduced in Lotus until release 2.0. You
may use this routine to write a worksheet that will be read by a
ver 1a copy of Lotus however. The values will be correct, but it
is impossible to display them in "time format" with version 1a.
The cell format indicator in 1a will display (?) instead of (D6)
as it does in 2.0+.
EXAMPLE:
CALL lTime$(TIME$)
'would write the system time to the current spreadsheet cell.
ERRORS:
File not open: lTime1 will return a long int (TIMER) (CP 0)
lTime2 will take no action. (CP 0)
Invalid time$: String passed is written to the file. (CP +)
61
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lTimeNow
COMPATIBILITY: Release 2.0+ only.
CELL POINTER: Advanced to next column.
SYNTAX: lTimeNow
lTimeNow will write the Lotus 1-2-3 version 2+ formula @NOW in
the current cell. In addition, the cell is formatted to display
in the currently set time format.
Use lToday to write the @NOW formula with a date display.
EXAMPLE:
CALL lWriteStr("The Current date is:", errCode)
CALL lRight(2) 'adjust for long label cell
CALL lToday
CALL lWriteStr("The Current time is:", errCode)
CALL lRight(2) 'adjust for long label cell
CALL lTimeNow 'will display fresh time each re-calc.
ERRORS:
No file open: No action taken.
62
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lToday
COMPATIBILITY: All Releases.
CELL POINTER: Advanced to next column.
SYNTAX: lToday
Writes the Lotus function @TODAY/@NOW to the current cell. The
cell is formatted for dd-mmm-yy display (D1) if there is no
default date format set. The system date will be displayed in
the target cell when the worksheet is loaded into Lotus.
When loaded by 1-2-3 release 2.0+, the @TODAY function is con-
verted to @NOW automatically.
See lTimeNow for an example.
ERRORS:
No file open: No action taken.
63
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lUp
COMPATIBILITY: All Releases.
CELL POINTER: Advanced to next column.
SYNTAX: lUp(count%)
count% is the number of rows.
lUp is the equivalent of hitting the up arrow key in Lotus. The
next row to be written will be decreased by the number of rows
the routine is called with. Attempts to 'cursor' above beyond
row 0 will leave the cursor in row 0.
EXAMPLE: move% = 5
CALL lUp(move%)
'The next cell to be written will be 5 rows
'above the current row, in the same column.
ERRORS:
No file open: No action taken.
Current column - count% < 0: Column set to 0.
64
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lWriteErr
COMPATIBILITY: All Releases.
CELL POINTER: Advanced to next column.
SYNTAX: lWriteErr
This routine is used internally to write the function @ERR to the
current cell when you do something out of line, like pass parame-
ters that are outside of the allowed range. Seems only fair that
you should be able to call it when an end user of your program
does something out of line.
I know, end users always respond to input prompts in only the
ways we programmers expect, but it's here just in case.
ERRORS:
No file open: No action taken.
65
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lWriteInt
NAME: lWriteNum
COMPATIBILITY: All Releases.
CELL POINTER: Advanced to next column.
SYNTAX: lWriteInt(integerVal%, errCode%)
SYNTAX: lWriteNum(doubleVal#, errCode%)
OK, so the meat and potatoes come near the end of the documenta-
tion. It's alphabetical.
Both these routines write a value to the current cell. There
probably isn't a need for two routines to write a numeric value
to a cell. The only difference is the resultant length of the
data being written. If your application is reading from a random
access file, where you know the data value is sure to be a two
byte signed integer, use lWriteInt for some space saving.
Any applications that need the full range of numbers should call
the lWriteNum routine instead, to maintain decimal values. Note
that this routine requires an eight byte double precision value
though, not a 4 byte single precision value.
ERRORS:
No file open: errCode returned as -1.
Disk Full: errCode returned as 1.
DOS errors: Dos error number returned in errCode.
66
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
NAME: lWriteStr
COMPATIBILITY: All Releases.
CELL POINTER: Advanced to next column.
SYNTAX: lWriteStr(dat$, errCode%)
dat$ string data to be written.
errCode% a non-zero return indicates an error.
Writes a label (string data) to the target cell.
One item of note is that the currently set label prefix charac-
ter, (single quote upon startup, or what you define as default
with lSetStrForm) is used if the data string sent to this routine
does not include a label prefix character as it's first byte. In
other words, if you call this routine with a data string that has
a valid label prefix character in the leftmost position, that
prefix character will take preference.
This routine will not actually write a null ("") cell. If called
with a null string, it will advance the internal column pointer
to the next column. Strings of ASCII 0's or space (32) charac-
ters are written to the file. This is a change from earlier
versions of 123-Write.
EXAMPLE:
CALL lFileOpen("D:\LOTUS\TESTFILE", errCode%)
'default label prefix=" ' " left alignment.
'we're at cell A1, write a title, will be left aligned.
CALL lWriteStr("Test worksheet written by 123-Write", errCode%)
CALL lLineFeed 'Then write the column headings in row 1...
CALL lWriteStr("^Name", errCode%) 'Centered
CALL lWriteStr("^Address", errCode%) 'Centered
CALL lWriteStr(CHR$(34) + "Balance", errCode%) 'Right justify
CALL lLineFeed
'Next the ever popular "=======" repeating character.
FOR i = 1 TO 3
CALL lWriteStr("\=", errCode%)
NEXT
CALL lLineFeed
ERRORS:
No file open: errCode returned as -1.
Disk Full: errCode returned as 1.
DOS errors: Dos error number returned in errCode.
67
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
ERRORS.....From Lotus 1-2-3
There really shouldn't be any when you retrieve a worksheet in
Lotus, if there is advise me as soon as possible, and include a
printout of the code if possible. See below on contacting me.
There is a series of real nasty error messages that I encountered
developing these routines. Lotus beeps, prints an error message
"Worksheet Revision out of date. Press HELP!" is one that comes
to mind, there are a number of others too! When you press F1,
the help key, a message comes up advising you to call Lotus
Customer Support right away. Don't panic, I know I never do. Go
over your code and be sure that everything's being called proper-
ly, then if need be, write me at the address on the registration
form.
The @ISAAF and @ISAPP functions were added with 1-2-3 Release
2.2. If they are used with Release 2.0 there will be a "Formula
Recalculation Error" message upon loading the file. This is to
be expected, loading in a file created by 1-2-3 2.2 would cause
the same errors. If you're not sure of the target release of
1-2-3, be careful of which functions you write.
123-Write ERRORS....
The only errors that will be returned by 123-Write are the few
codes returned by some of the more frequently used routines.
Anything else is blissfully ignored. I'm trying to make a trade
off in the number of parameters that have to be passed with the
frequency of the routines. If a routine is only to be called
once or twice in an entire file write, I felt it was not impor-
tant to trap DOS errors. A routine that would be called multiple
times, I felt, was the more likely candidate for error traps.
BASIC ERRORS:
These are yours to trap or ignore or fix. The particular BASIC
environment you're running under will dictate what happens. For
example, try lFileOpen with a floppy disk file as the target, and
the drive door open.
In QB or QBX, the environment reports the error, even though the
error happens in a Quick Library. From the DOS command line, the
run-time library will catch the error before it gets passed back
to your program in non-stand alone programs. If the program is
compiled with /O, the DOS critical error handler steps in with
the "Abort Retry Fail" message.
So, I believe that if you have error trapping enabled, errors
that occur in the 123-Write routines may cause that trap routine
to be called (runtime module should...). Short of having a drive
door open, I can't envision any way a user can mess up while
running these routines. (Where have I heard those famous last
words before?)
68
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
CONTACTING the author:
Via Modem:
Questions can be answered via a call to The Off Hour Rockers BBS,
201-727-6917 or 201-727-6785 (N81, 9600/2400/1200) a PCBoard BBS.
The Sysop was kind enough to give me a conference to ramble away
in, so use the J)oin command and select the BASIC conference.
Leave a message to TOM VOUGHT, and it will be answered ASAP.
Also, Compuserve ID: 70054,1367 (infrequent)
Otherwise:
Write me at: 5 John Street
Morganville, NJ 07751
FINIS:
I hope that these routines make your coding easier, and at the
same time give you the tools to improve your programs. If you
enjoy them, let me know, if there's something you don't like,
think should be improved, or want to see included etc, let me
know. Anyway, enjoy!
<< Tom >>
69
123-Write ver 2.0
Copyright (c) 1990, Thomas J. Vought
APPENDIX A:
Possible error codes returned by DOS.
This in not intended as a complete list of the possible error
codes that DOS might return, for that I can only refer you to a
good DOS reference work.
ERROR CODE MEANING
---------- -------
1 Disk Full. This is not generated by DOS, but
by 123-Write when DOS reports fewer
bytes written to disk then requested.
The file is left open, so a call to
lClose should be made before you termi-
nate your program.
2 File Not Found. lFileAppend, the path is
there, but the file isn't.
3 Path Not Found. lFileOpen or lFileAppend,
indicates an invalid drive or path spec.
Note that DOS's critical error handler may intercept some errors.
A "Drive not ready" error, for example, results in the familiar
A)bort R)etry F)ail message. If the user selects Abort, your
program may terminate, choosing Fail will result in an error code
being passed back to your program by 123-Write (3 in this in-
stance).
70
Routine reference.
1-2-3
Release Page
LBoolean (col1%, row1%, col2%, row2%, operation%), ALL 18
LCellFunction(dSeg%, dOfs%) MIXED 19
LCellMath(col1%, row1%, col2%, row2%, oper%) ALL 23
LClose ALL 24
LCol (column$) ALL 25
LColFunction (startRow%, endRow%, oper%) MIXED 26
LConstMath(col%, row%, constVal#, oper%) ALL 27
LDate(dat$) ALL 28
LDown(count%) ALL 29
LFileAppend(fileName$, ecode%) ALL 30
LFileOpen(fileName$, errCode%) ALL 32
LGetLoc(whatCol%, whatRow%) ALL 33
LGlobal(item%, switch%) ALL 34
LHideCol(col%, writeFlag%) 2.0+ 35
LLeft(count%) ALL 36
LLineFeed ALL 37
LLink(fileSpecRange$) 2.2 38
LMultiCellMath(dSeg%, dOfs%) ALL 40
LPrintBorders_ ALL 43
(rulc%, rulr%, rlrc%, rlrr%, culc%, culr%, clrc%, clrr%)
LPrintMargins(lm%, rm%, tm%, bm%, pageLen%) ALL 44
LPrintRange (ulc%, ulr%, lrc%, lrr%) ALL 45
LPrintSetup (setup$) ALL 46
LPrintTitle (title$, header%) ALL 47
LRange(rangeName$, ulc%, ulr%, lrc%, lrr%) ALL 48
LRight(count%) ALL 49
LRowFunction(startCol%, endCol%, oper%) MIXED 50
LSetCellForm(formatCode%, decimals%) MIXED 51
LSetCol(col%, colWidth%) ALL 53
LSetCols(colWidths%()) ALL 54
LSetDateForm (format%, protect%) MIXED 55
LSetLoc(newCol%, newRow%) ALL 56
LSetStrForm(formatCode%) MIXED 57
LSetTimeForm (format%, protect%) 2.0+ 58
LSortKey (primry%, ulc%, ulr%, lrc%, lrr%, ascnd%) ALL 59
LSortRange(ulc%, ulr%, lrc%, lrr%) ALL 60
LTime(timeStr$) 2.0+ 61
LTimeNow 2.0+ 62
LToday ALL 63
LUp(count%) ALL 64
LWriteErr ALL 65
LWriteInt(integerVal%, errCode%) ALL 66
LWriteNum(doubleVal#, errCode%) ALL 66
LWriteStr(dat$, errCode%) ALL 67
71